﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class NewsDAO
    {
        //添加新闻
        public Boolean updateNews(int id,string newsTitle, string Detail)
        {
            SqlConnection conn = null;
            bool state = false;
            try
            {

                conn = SQLManager.GetConn();
                conn.Open();

                Console.WriteLine(conn.State);

                SqlCommand cmd = new SqlCommand("update news set Title=@Title, Detail=@Detail where id=@id", conn);
                cmd.Parameters.Add(new SqlParameter("@Title", newsTitle));
                cmd.Parameters.Add(new SqlParameter("@Detail", Detail));
                cmd.Parameters.Add(new SqlParameter("@id", id));
                if (cmd.ExecuteNonQuery() == 1)
                {
                    state = true;
                }
                conn.Close();
                conn.Dispose();
            }
            catch (Exception)
            {
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return state;
        }





        //添加新闻
        public Boolean addNews(string newsTitle, string Detail, int issueUser, int sectionId, int outLineState)
        {
            SqlConnection conn = null;
            bool state = false;
            try
            {
                Console.WriteLine("aaaa");
                conn = SQLManager.GetConn();
                conn.Open();

                Console.WriteLine(conn.State);

                SqlCommand cmd = new SqlCommand("insert into news(Title, Detail,  IssueUser, SectionId, State) values(@Title, @Detail,  @IssueUser, @SectionId, @State)", conn);
                cmd.Parameters.Add(new SqlParameter("@Title", newsTitle));
                cmd.Parameters.Add(new SqlParameter("@Detail", Detail));
                cmd.Parameters.Add(new SqlParameter("@IssueUser", issueUser));
                cmd.Parameters.Add(new SqlParameter("@SectionId", sectionId));
                cmd.Parameters.Add(new SqlParameter("@State", outLineState));
                
            if (cmd.ExecuteNonQuery() == 1)
            {
                state = true;
            }
            conn.Close();
            conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return state;
        }
        //读取新闻详细内容

        public List<News> getNews(int id) {
            List<TarreOA.TarreOAModels.News> list = new List<TarreOA.TarreOAModels.News>();
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "select * from news where id="+id ;
                conn.Open();
                SqlDataReader dr = sc.ExecuteReader();
                while (dr.Read())
                {
                    TarreOA.TarreOAModels.News news = new TarreOA.TarreOAModels.News();
                    news.Detail=(string)dr["detail"];
                    news.IssueTime = dr["newsdate"].ToString() ;
                    news.Title=dr["title"].ToString();
                    list.Add(news);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return list;
        
        }






        public News showNews(int id) {
            SqlConnection conn = null;
            SqlDataReader dr = null;
            News news = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();

                SqlCommand cmd = new SqlCommand("select * from news where id= @id", conn);
                cmd.Parameters.Add(new SqlParameter("@id",id));
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    news = new News();
                    news.Title = dr["title"].ToString();
                    news.Detail = (String)dr["detail"];
                 
                }
                dr.Close();
                conn.Close();
                dr.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return news;
        }
        // 修改新闻状态
        public Boolean changeState(String id)
        {
            SqlConnection conn = null;
            //SqlDataReader dr = null;
            Boolean flag = false;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("update news set state=(state+1)%2 where id=@id", conn);
                cmd.Parameters.Add(new SqlParameter("@id",id));
                if (cmd.ExecuteNonQuery() == 1)
                {
                    flag = true;
                }
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return flag;
        }
        //按id删除新闻
        public Boolean deleteById(int id)
        {
            SqlConnection conn = null;
            //SqlDataReader dr = null;
            Boolean flag = false;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("delete from news where id=@id", conn);
                cmd.Parameters.Add(new SqlParameter("@id", id));
                if (cmd.ExecuteNonQuery() == 1)
                {
                    flag = true;
                }
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return flag;
        }

        //得到用户的文件夹名称
        public String getUserSpacePath(int id)
        {
            SqlConnection conn = null;
            SqlDataReader dr = null;
            String detail = "您访问的内容不存在或已被删除";
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();

                SqlCommand cmd = new SqlCommand("select detail from usertb where id= @id", conn);
                cmd.Parameters.Add(new SqlParameter("@id", id));
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    detail = (String)dr["detail"];

                }
                dr.Close();
                conn.Close();
                dr.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return detail;
        }
    }
}
